package com.aaa.qy127.dao.impl;

import com.aaa.qy127.dao.BookDao;
import com.aaa.qy127.entity.Book;
import com.aaa.qy127.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("bookDaoImpl")
public class BookDaoImpl implements BookDao {

    /**
     * 查询全部，并分页
     * @return
     */
    @Override
    public List<Map<String,Object>> getAllBook(Book book) {

        StringBuffer sql = new StringBuffer("select bookid,bookname,author,count,remark,publisher,price,status,tb.typeid,typename,tb.url from t_book tb,t_type tp where tb.typeid=tp.typeid");
        if (book.getBookname() != null){
            sql.append(" and tb.bookname like '%").append(book.getBookname()+"%'");
        }

        if (book.getAuthor() != null){
            sql.append(" and tb.author like '%").append(book.getAuthor()+"%'");
        }

        //System.out.println(book.getTypeid());

        //由于从页面传回来的 typeid 再经过 BeanUtils.populate(); 封装的时候 变成了0 所以此时 也要判断一下 不等于0 的情况
        if (book.getTypeid() != null && book.getTypeid() !=0){
            sql.append(" and tb.typeid = ").append(book.getTypeid());
        }

        sql.append(" limit ?,?");

        //System.out.println(sql);

        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql.toString(),(book.getPage()-1)*book.getLimit(),book.getLimit());
        //System.out.println("============================"+sql);
        return maps;
    }

    /**
     * 查询全部书籍的数量
     * @return
     */
    @Override
    public int getcount(Book book) {
        StringBuffer sql = new StringBuffer("select count(*) from t_book tb where 1=1");
        if (book.getBookname() != null){
            sql.append(" and tb.bookname like '%").append(book.getBookname()+"%'");
        }

        if (book.getAuthor() != null){
            sql.append(" and tb.author like '%").append(book.getAuthor()+"%'");
        }

        if (book.getTypeid() != null && book.getTypeid() !=0){
            sql.append(" and tb.typeid =").append(book.getTypeid());
        }

        List<List> lists = JDBCUtil.queryList(sql.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }

    /**
     * 插入一条书籍信息
     * @param book
     * @return
     */
    @Override
    public int insertOneBook(Book book) {
        String sql = "insert into t_book values(null,?,?,?,?,?,?,?,?,?)";
        //int i = JDBCUtil.update(sql, book.getBookname(), book.getAuthor(), 0, book.getRemark(), book.getTypeid(), book.getPublisher(), book.getPrice(), 0,book.getUrl());
        int i = JDBCUtil.getPrimaryKey(sql, book.getBookname(), book.getAuthor(), 0, book.getRemark(), book.getTypeid(), book.getPublisher(), book.getPrice(), 0,null);
        //System.out.println(i);
        return i;
    }

    /**
     * 按照bookid 修改 book 表中的 url
     * @param book
     */
    @Override
    public void updateUrlByBookId(Book book) {
        String sql = "update t_book set url =? where bookid =?";
        int i = JDBCUtil.update(sql, book.getUrl(),book.getBookid());
    }

    /**
     * 通过bookid 修改 status的值
     * 当status的值为1（上架） 的时候 ，就把status的值改为0（下架）
     * 反之亦然
     * @param bookid
     * @param status
     * @return
     */
    @Override
    public int updataStatus(Integer bookid, Integer status) {
        //判断传过来的status的值是否是0，如果是0 则改为1，如果不是就改为0
        if (status == 0) {
            status = 1;
        }else {
            status =0;
        }
        String sql = "update t_book set status =? where bookid =?";
        int i = JDBCUtil.update(sql, status, bookid);
        return i;
    }

    /**
     * 通过bookid修改一条数据
     * @param book
     * @return
     */
    @Override
    public int updateBook(Book book) {
        String sql = "update t_book set bookname=?,author=?,remark=?,typeid=?,publisher=?,price=?,url=? where bookid=?";
        int i = JDBCUtil.update(sql, book.getBookname(), book.getAuthor(), book.getRemark(), book.getTypeid(), book.getPublisher(), book.getPrice(), book.getUrl(),book.getBookid());
        return i;
    }

    /**
     * 通过 书名 书的id  书的出版社 查询
     * @param book
     * @return
     */
    @Override
    public List<Map<String, Object>> getOneBookByBookidAndPublisherAndBookname(Book book) {
        String sql = "select bookid,bookname,author,count,remark,typeid,publisher,price,`status`,url from t_book where bookid = ? and bookname =? and publisher = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, book.getBookid(), book.getBookname(), book.getPublisher());
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 根据bookid 查询 该书籍 的图片信息
     * @return
     */
    @Override
    public List<Map<String, Object>> getBookUrlBybookid(Book book) {
        String sql = "select id,url,bookid,`status` from t_book_tp where bookid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, book.getBookid());
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 根据bookid 删除 该用户的 所有图片信息
     * @param book
     * @return
     */
    @Override
    public int deleteBookUrlByBookid(Book book) {
        String sql = "delete from t_book_tp where bookid = ?";
        int i = JDBCUtil.update(sql, book.getBookid());
        return i;
    }

    /**
     * 通过书名 查询数据
     * @param book
     * @return
     */
    @Override
    public List<Map<String, Object>> getOneBook(Book book) {
        String sql = "select bookid,bookname,author,count,remark,typeid,publisher,price,status from t_book where bookname = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, book.getBookname());
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 按照 出版社 查询书籍信息
     * @param book
     * @return
     */
    @Override
    public List<Map<String, Object>> getBookByPublisher(Book book) {
        String sql = "select bookid,bookname,author,count,remark,typeid,publisher,price,status from t_book where publisher = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, book.getPublisher());
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    /**
     * 通过书籍的 id 查询 书籍的 数量
     * @param bookid
     * @return
     */
    @Override
    public int getCountByBookId(Integer bookid) {
        String sql = "select count from t_book where bookid = ?";
        List<List> lists = JDBCUtil.queryList(sql, bookid);
        if (lists.size()>0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    /**
     * 向 t_book_tp表中 添加 长传的 图片的地址
     * @param book
     */
    @Override
    public void insertAllUrl(Book book) {
        String url = book.getUrl();
        String[] split = url.split(",");
        //System.out.println("dao 类 "+Arrays.toString(split));
        for (int i = 0; i < split.length; i++) {
            if (i==0){
                String sql ="insert INTO t_book_tp VALUES(null,?,?,1)";
                JDBCUtil.update(sql,split[i],book.getBookid());
            }else {
                String sql ="insert INTO t_book_tp VALUES(null,?,?,0)";
                JDBCUtil.update(sql,split[i],book.getBookid());
            }
        }
    }

    /**
     * 通过 bookid 和 t_book_tp中 的status 查询 图片路径
     * @param book
     * @return
     */
    @Override
    public String getOneBookUrl(Book book) {
        String sql = "select tbt.url from t_book tb INNER JOIN t_book_tp tbt ON tb.bookid = tbt.bookid where tb.bookid = ? and tbt.status = 1";
        List<List> lists = JDBCUtil.queryList(sql, book.getBookid());
        //System.out.println("dao 类 lists"+lists);
        if (lists.size() >0){
            Object o = lists.get(0).get(0);
            String s = o + "";
           //System.out.println("dao 类 url"+s);
            return s;
        }
        return null;
    }

    /**
     * 通过booid 查询出 该书籍的所有的 图片
     * @param bookid
     * @return
     */
    @Override
    public List<Map<String, Object>> getImgByBookId(String bookid) {
        String sql = "select tbt.id,tbt.url,tbt.bookid,tbt.status from t_book_tp tbt where bookid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, bookid);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 通过 url 删除 t_book_tp 表中的 书籍图片
     * @param url
     * @return
     */
    @Override
    public int deleteBookUrl(String url) {
        String sql = "delete from t_book_tp where url = ?";
        int i = JDBCUtil.update(sql, url);
        return i;
    }

    /**
     * 通过 url 修改 book 表中的url
     * @param url
     */
    @Override
    public void updateBookUrlByUrl(String url) {
        String sql = "update t_book set url=NULL where url =?";
        JDBCUtil.update(sql, url);
    }

    /**
     * 通过 url 查询 book表中的 url
     * @param url
     * @return
     */
    @Override
    public String getOneBookUrlByurl(String url) {
        String sql = "select url from t_book where url=?";
        List<List> lists = JDBCUtil.queryList(sql,url);
        if (lists.size() >0){
            Object o = lists.get(0).get(0);
            String s = o + "";
            return s;
            }
        return "空";
    }

    /**
     * 通过 原来的图片的路径查询 book表中的图片路径
     * @param urlOld
     * @return
     */
    @Override
    public String getOneBookUrlByUrlOld(String urlOld) {
        String sql = "select url from t_book where url=?";
        List<List> lists = JDBCUtil.queryList(sql, urlOld);
        if (lists.size()>0){
            Object o = lists.get(0).get(0);
            String s = o + "";
            return s;
        }
        return "空";
    }

    /**
     * 通过老的 照片的路径 修改 book表中新的照片的路径
     * @param urlNew
     * @param urlOld
     * @return
     */
    @Override
    public int updateBookUrlByUrlOldAndUrlNew(String urlNew, String urlOld) {
        String sql = "update t_book set url = ? where url =?";
        int i = JDBCUtil.update(sql, urlNew, urlOld);
        return i;
    }

    /**
     * 通过老的 照片的路径 修改 book_tp表中新的照片的路径
     * @param urlNew
     * @param urlOld
     * @return
     */
    @Override
    public int updateBookTpUrlByUrlOldAndNew(String urlNew, String urlOld) {
        String sql ="update t_book_tp set url = ? where url = ?";
        int i = JDBCUtil.update(sql, urlNew, urlOld);
        return i;
    }

    /**
     * 查询所有的书籍的名字 和 id 通过 typeid 查询 书籍的名字 和 id
     * @return
     */
    @Override
    public List<Map<String,Object>> selectBookNameAndBookid(String typeid) {
        String sql = "select bookid,bookname,author,publisher from t_book where typeid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, typeid);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    /**
     * 通过 bookid 查询 这个书籍的 出版社
     * @param bookid
     * @return
     */
    @Override
    public List<Map<String, Object>> selectPublisherAndBookid(String bookid) {
        String sql = "select bookid,publisher,author,count,price from t_book where bookid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, bookid);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 获取到 所有的 书籍的信息
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllBook() {
        String sql = "select tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tt.typename,tb.publisher,tb.price,tb.`status`,tb.url from t_book tb INNER JOIN t_type tt ON tb.typeid = tt.typeid where status = 1 limit 0,10";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 通过booid 获取到 这一条信息
     * @param bookid
     * @return
     */
    @Override
    public List<Map<String, Object>> showBookByBookid(String bookid) {
        String sql = "select tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tt.typename,tb.publisher,tb.price,tb.`status`,tb.url from t_book tb INNER JOIN t_type tt ON tb.typeid = tt.typeid where bookid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, bookid);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 通过 bookid 获取 该 书籍的 所有的图片
     * @param bookid
     * @return
     */
    @Override
    public List<Map<String, Object>> showBookUrlByBookid(String bookid) {
        String sql = "select id,url,bookid,`status` from t_book_tp where bookid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, bookid);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 通过 typid 查询出 该 类型的 书籍 的 信息
     * @param typeid
     * @return
     */
    @Override
    public List<Map<String, Object>> showBookBytypeid(String typeid) {
        String sql = "select tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tt.typename,tb.publisher,tb.price,tb.status,tb.url from t_book tb INNER JOIN t_type tt ON tb.typeid = tt.typeid where tt.typeid = ? and tb.status = 1";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, typeid);
        if (maps.size()>0){
            return maps;
        }
        ArrayList<Map<String, Object>> maps1 = new ArrayList<>();
        HashMap<String, Object> map = new HashMap<>();
        Object bookid = map.put("bookid", 0);
        boolean add = maps1.add(map);
        return maps1;
    }

    /**
     * 按照 bookname 模糊查询
     * @param bookname
     */
    @Override
    public List<Map<String, Object>> selectSomeBookBybookname(String bookname) {
        //select tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tt.typename,tb.publisher,tb.price,tb.`status`,tb.url from t_book tb INNER JOIN t_type tt ON tb.typeid = tt.typeid where tb.bookname like '%高%';
        /*StringBuffer sql = new StringBuffer("select tb.bookid,tb.bookname,tb.author,tb.count,tb.remark,tb.typeid,tt.typename,tb.publisher,tb.price,tb.`status`,tb.url from t_book tb INNER JOIN t_type tt ON tb.typeid = tt.typeid where 1=1");
        sql.append(" and tb.bookname like '%").append(bookname+"%'");*/
        String sql = "select b.*,typename from t_book b JOIN t_type t on b.typeid=t.typeid where bookname like '%"+bookname+"%' and status = 1 \n" +
                "union \n" +
                "select b.*,typename from t_book b JOIN t_type t on b.typeid=t.typeid where typename like '%"+bookname+"%' and status = 1 \n" +
                "union \n" +
                "select b.*,typename from t_book b JOIN t_type t on b.typeid=t.typeid where author like '%"+bookname+"%' and status = 1 \n" +
                "union \n" +
                "select b.*,typename from t_book b JOIN t_type t on b.typeid=t.typeid where publisher like '%"+bookname+"%' and status = 1";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql);
        //System.out.println("daoimpl"+maps);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }
}
